package pl.qsoft.db.storedProc.statement;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Time;
import java.util.Map;

import pl.qsoft.db.annotation.StoredProcedureDefinition;
import pl.qsoft.db.annotation.StoredProcedureParam;
import pl.qsoft.db.exception.StoredProcedureInvocationException;
import pl.qsoft.db.storedProc.complex.ComplexObjectBuilder;
import static pl.qsoft.db.annotation.StoredProcedureParam.ParamKind;
import static pl.qsoft.db.annotation.StoredProcedureParam.VariableType;

/**
 * Implementation of {@link StatementBuilder} interface for Oracle databases. It prepares callable statement for execution by defining input parameters and its values as well as by registering output parameters for data retrieval after the statement execution.
 * It is also responsible for database type mappings.
 * @author Wojciech Kudla
 */
public class OracleStatementBuilder implements StatementBuilder {
	
	private static final int NULL = oracle.jdbc.OracleTypes.NULL; 

	/**
	 * Main method used by the component client
	 * @see StatementBuilder
	 */
	public CallableStatement buildStatement(CallableStatement stmt, StoredProcedureDefinition proc, StoredProcedureParam[] params, Map<Object, Object> values) throws SQLException {
		insertParameters(stmt, params, values);
		return stmt;
	}

	
	/**
	 * Inserts all input and output parameters with values into the statement
	 * @param stmt Callable statement
	 * @param params Parameter definitions
	 * @param values Parameter name/index -> value map
	 */
	protected void insertParameters(CallableStatement stmt, StoredProcedureParam[] params, Map<Object, Object> values) {
		for (StoredProcedureParam param : params) {
			Object key = !param.paramName().equals("") ? param.paramName() : param.paramNumber();
			Object value = values.get(key);
			if (isValidParam(param, ParamKind.IN)) {
				if (key instanceof String) insertNamedInputParam(stmt, param, value);
				else insertIndexedInputParam(stmt, param, value);
			} else {
				if (key instanceof String) insertNamedOutputParam(stmt, param);
				else insertIndexedOutputParam(stmt, param);
			}
		}
	}
	
	
	/**
	 * Converts generic database data types defined in {@link VariableType} to Oracle types
	 * @param type Generic type
	 * @return Oracle type
	 */
	protected int toOracleType(StoredProcedureParam.VariableType type) {
		int result;		
		if (type.equals(VariableType.VARCHAR)) result = oracle.jdbc.OracleTypes.VARCHAR;
		else if (type.equals(VariableType.NUMBER)) result = oracle.jdbc.OracleTypes.NUMBER;
		else if (type.equals(VariableType.DATE)) result = oracle.jdbc.OracleTypes.DATE;
		else if (type.equals(VariableType.CURSOR)) result = oracle.jdbc.OracleTypes.CURSOR;
		else if (type.equals(VariableType.DATETIME)) result = oracle.jdbc.OracleTypes.TIME;
		else if (type.equals(VariableType.TIMESTAMP)) result = oracle.jdbc.OracleTypes.TIMESTAMP;
		else if (type.equals(VariableType.TIMESTAMP)) result = oracle.jdbc.OracleTypes.TIMESTAMP;
		else if (type.equals(VariableType.BLOB)) result = oracle.jdbc.OracleTypes.BLOB;
		else if (type.equals(VariableType.CLOB)) result = oracle.jdbc.OracleTypes.CLOB;
		else if (type.equals(VariableType.BOOLEAN)) result = oracle.jdbc.OracleTypes.BOOLEAN;
		else result = oracle.jdbc.OracleTypes.VARCHAR;
		return result;
	}
	

	/**
	 * Registers single indexed output parameter
	 * @param stmt Callable statement
	 * @param param Stored procedure parameter definition
	 */
	private void insertIndexedOutputParam(CallableStatement stmt, StoredProcedureParam param) {
		try {
			stmt.registerOutParameter(param.paramNumber(), toOracleType(param.variableType()));
		} catch (SQLException e) {
			throw new StoredProcedureInvocationException("Cannot register output parameter " + param.paramName(), e);
		}
	}

	
	/**
	 * Registers single named output parameter
	 * @param stmt Callable statement
	 * @param param Stored procedure parameter definition
	 */
	private void insertNamedOutputParam(CallableStatement stmt, StoredProcedureParam param) {
		try {
			stmt.registerOutParameter(param.paramName(), toOracleType(param.variableType()));
		} catch (SQLException e) {
			throw new StoredProcedureInvocationException("Cannot register output parameter " + param.paramName(), e);
		}
	}

	
	/**
	 * Registers single indexed input parameter
	 * @param stmt Callable statement
	 * @param param Stored procedure parameter definition
	 * @param value Value to be inserted
	 */
	private void insertIndexedInputParam(CallableStatement stmt, StoredProcedureParam param, Object value) {
		try {
			int key = param.paramNumber();
			if (param.variableType() == VariableType.COMPLEX) {
				ComplexObjectBuilder builder = param.complexObjectBuilderClass().newInstance();
				builder.setConnection(stmt.getConnection());
				stmt.setObject(key, builder.buildObject(value));
			}
			else if(value == null) stmt.setNull(key, NULL);
			else if (value instanceof String) stmt.setString(key, (String) value);
			else if (value instanceof Integer) stmt.setInt(key, (Integer) value);
			else if (value instanceof Double) stmt.setDouble(key, (Double) value);			
			else if (value instanceof Float) stmt.setFloat(key, (Float) value);
			else if (value instanceof BigDecimal) stmt.setBigDecimal(key, (BigDecimal) value);
			else if (value instanceof Long) stmt.setLong(key, (Long) value);
			else if (value instanceof Date) stmt.setDate(key, (Date) value);
			else if (value instanceof Time) stmt.setTime(key, (Time) value);
			else if (value instanceof Boolean) stmt.setBoolean(key, (Boolean) value);
			else stmt.setObject(key, value);
		} catch (Exception e) {
			throw new StoredProcedureInvocationException("Cannot set statement value", e);
		}
	}

	
	/**
	 * Registers single named input parameter
	 * @param stmt Callable statement
	 * @param param Stored procedure parameter definition
	 * @param value Value to be inserted
	 */
	private void insertNamedInputParam(CallableStatement stmt, StoredProcedureParam param, Object value) {
		try {
			String key = param.paramName();
			if (param.variableType() == VariableType.COMPLEX) {
				ComplexObjectBuilder builder = param.complexObjectBuilderClass().newInstance();
				builder.setConnection(stmt.getConnection());
				stmt.setObject(key, builder.buildObject(value));
			}
			else if (value == null) stmt.setNull(key, NULL);
			else if (value instanceof String) stmt.setString(key, (String) value);
			else if (value instanceof Integer) stmt.setInt(key, (Integer) value);
			else if (value instanceof Double) stmt.setDouble(key, (Double) value);
			else if (value instanceof Float) stmt.setFloat(key, (Float) value);
			else if (value instanceof BigDecimal) stmt.setBigDecimal(key, (BigDecimal) value);
			else if (value instanceof Long) stmt.setLong(key, (Long) value);
			else if (value instanceof Date) stmt.setDate(key, (Date) value);
			else if (value instanceof Time) stmt.setTime(key, (Time) value);
			else if (value instanceof Boolean) stmt.setBoolean(key, (Boolean) value);			
			else stmt.setObject(key, value);
		} catch (Exception e) {
			throw new StoredProcedureInvocationException("Cannot set statement value", e);
		}
	}
		
	
	/**
	 * Checks whether given stored procedure parameter is valid (it has non-empty name or index)
	 * @param param Stored procedure parameter 
	 * @param kind Parameter kind
	 * @return True if parameter is found valid 
	 */
	protected boolean isValidParam(StoredProcedureParam param, StoredProcedureParam.ParamKind kind) {
		return param.paramKind().equals(kind) && (!param.paramName().equals("") || param.paramNumber() != -1);
	}
	
}
